﻿Imports System.Data
Imports System.Data.SqlClient
Imports System.ComponentModel
Public Class HocSinhDAO
    Public Sub LoadCombobox(ByVal cbx As ComboBox, ByVal sql As String)
        Dim load As DataProvider = New DataProvider
        cbx.DataSource = load.Sql_ExecuteTable(sql)
    End Sub
    Public Sub LoadGridView(ByVal dgv As DataGridView, ByVal sql As String)
        Dim load As DataProvider = New DataProvider
        dgv.DataSource = load.Sql_ExecuteTable(sql)
    End Sub
    Public Function ThemHocSinh(ByVal hsInsert As HocSinhDTO) As Boolean
        Dim Insert As DataProvider = New DataProvider
        Dim sql As String = String.Format("Insert_HocSinh")
        Dim pr As SqlParameter() = {New SqlParameter("@MaHocSinh", hsInsert.MaHocSinh),
                                    New SqlParameter("@HoTen", hsInsert.HoTen),
                                    New SqlParameter("@NgaySinh", hsInsert.NgaySinh),
                                    New SqlParameter("@DiaChi", hsInsert.DiaChi),
                                    New SqlParameter("@QueQuan", hsInsert.QueQuan),
                                    New SqlParameter("@TonGiao", hsInsert.TonGiao),
                                    New SqlParameter("@Email", hsInsert.Email),
                                    New SqlParameter("@GioiTinh", hsInsert.GioiTinh),
                                    New SqlParameter("@MaLop", hsInsert.MaLop)}
        Dim cmd As SqlCommand = New SqlCommand(sql)
        cmd.CommandType = CommandType.StoredProcedure
        For i As Integer = 0 To pr.Count() - 1
            cmd.Parameters.Add(pr(i))
        Next
        Return Insert.SQL_ExecuteNonQuery(cmd)
    End Function
    Public Function XoaHocSinh(ByVal id As String) As Boolean
        Dim Remove As DataProvider = New DataProvider
        Dim sql As String = String.Format("Remove_HocSinh")
        Dim pr As SqlParameter = New SqlParameter("@MaHocSinh", id)
        Dim cmd As SqlCommand = New SqlCommand(sql)
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Parameters.Add(pr)
        Return Remove.SQL_ExecuteNonQuery(cmd)
    End Function
    Public Function SuaHocSinh(ByVal hsUpdate As HocSinhDTO) As Boolean
        Dim Update As DataProvider = New DataProvider
        Dim sql As String = String.Format("Update_HocSinh")
        Dim pr As SqlParameter() = {New SqlParameter("@MaHocSinh", hsUpdate.MaHocSinh),
                                    New SqlParameter("@HoTen", hsUpdate.HoTen),
                                    New SqlParameter("@NgaySinh", hsUpdate.NgaySinh),
                                    New SqlParameter("@DiaChi", hsUpdate.DiaChi),
                                    New SqlParameter("@QueQuan", hsUpdate.QueQuan),
                                    New SqlParameter("@TonGiao", hsUpdate.TonGiao),
                                    New SqlParameter("@Email", hsUpdate.Email),
                                    New SqlParameter("@GioiTinh", hsUpdate.GioiTinh),
                                    New SqlParameter("@MaLop", hsUpdate.MaLop)}
        Dim cmd As SqlCommand = New SqlCommand(sql)
        cmd.CommandType = CommandType.StoredProcedure
        For i As Integer = 0 To pr.Count() - 1
            cmd.Parameters.Add(pr(i))
        Next
        Return Update.SQL_ExecuteNonQuery(cmd)
    End Function

    Public Function GetList() As BindingList(Of HocSinhDTO)
        Dim _ListHocSinh As New BindingList(Of HocSinhDTO)
        Dim GetDT As DataProvider = New DataProvider
        Dim sql As String = "select MaHocSinh, MaLop, HoTen, GioiTinh, NgaySinh, DiaChi, QueQuan, Email, TonGiao from HOCSINH"
        Dim rd As SqlDataReader
        rd = GetDT.SQL_ExcuteQueryReader(sql)
        While rd.Read()
            Dim item As New HocSinhDTO()
            item.MaHocSinh = rd.GetString(0)
            item.MaLop = rd.GetString(1)
            item.HoTen = rd.GetString(2) '1
            item.GioiTinh = rd.GetBoolean(3)
            item.NgaySinh = rd.GetDateTime(4) '2
            item.DiaChi = rd.GetString(5)
            item.QueQuan = rd.GetString(6)
            item.Email = rd.GetString(7)
            item.TonGiao = rd.GetString(8)
            _ListHocSinh.Add(item)
        End While
        Return _ListHocSinh
    End Function
End Class
